﻿Imports System.Data.SqlClient
Imports System.Data.SqlServerCe

Public Class DatabaseProvider

    Private Shared _instance As DatabaseProvider
    Private ReadOnly _connection As SqlCeConnection
    Private _query As String

    Protected Sub New()
        '_connection = New SqlClient.SqlConnection("Database=bartek;Server=.\SQLEXPRESS;Integrated Security=True;")
        Dim con As String = "Data Source=..\..\Database1.sdf"

        '_connection = New SqlClient.SqlConnection(con)
        _connection = New SqlCeConnection(con)
    End Sub

    Public Shared Function Instance() As DatabaseProvider
        If _instance Is Nothing Then
            _instance = New DatabaseProvider()
        End If
        Return _instance
    End Function

    Private Function InsertPersonAndGetId(values As Dictionary(Of String, String)) As Integer
        _query = String.Format("INSERT INTO person (name, surname, mobile, home_tel) VALUES ('{0}', '{1}', '{2}', '{3}')", values.Item("name"), values.Item("surname"), values.Item("mobile"), values.Item("home_tel"))

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection

        command.ExecuteNonQuery()
        command.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
        Return command.ExecuteScalar
    End Function

    Private Function InsertAddressAndGetId(values As Dictionary(Of String, String)) As Integer
        _query = String.Format("INSERT INTO address (town, postcode, line1, line2) VALUES ('{0}', '{1}', '{2}', '{3}')", values.Item("town"), values.Item("postcode"), values.Item("line1"), values.Item("line2"))

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection

        command.ExecuteNonQuery()
        command.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
        Return command.ExecuteScalar
    End Function

    Public Function InsertOrderLine(productId As Integer, orderId As Integer, quantity As Integer) As Dictionary(Of String, String)
        _query = String.Format("INSERT INTO Order_Product (order_id, product_id, quantity) VALUES ({0}, {1}, {2})", orderId, productId, quantity)

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection
        _connection.Open()
        command.ExecuteNonQuery()
        _connection.Close()

        Return GetProductById(productId)
    End Function

    Private Function GetProductById(id As Integer) As Dictionary(Of String, String)
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlCeDataAdapter()
        dataAdapter.SelectCommand = New SqlCeCommand(String.Format("SELECT * FROM product WHERE id = {0}", id))
        dataAdapter.SelectCommand.Connection = _connection
        _connection.Open()
        dataAdapter.Fill(dataSet, "product")
        _connection.Close()

        Dim dc As New Dictionary(Of String, String)
        dc.Add("ProductId", dataSet.Tables(0).Rows(0).Item(0))
        dc.Add("ProductName", dataSet.Tables(0).Rows(0).Item(1))
        dc.Add("ProductPrice", dataSet.Tables(0).Rows(0).Item(2))

        Return dc
    End Function

    Public Function InsertOrderAndGetId(customerId As Integer, employeeId As Integer) As Integer
        _query = String.Format("INSERT INTO [Order] (customer_id, employee_id, date) VALUES ({0}, {1}, GETDATE())", customerId, employeeId)

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection
        _connection.Open()
        command.ExecuteNonQuery()
        command.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
        Dim orderId = command.ExecuteScalar()
        _connection.Close()

        Return orderId
    End Function

    Public Sub InsertCustomer(values As Dictionary(Of String, String))
        _connection.Open()
        Dim personId = InsertPersonAndGetId(values)
        Dim addressId = InsertAddressAndGetId(values)
        _query = String.Format("INSERT INTO Customer (address_id, person_id) VALUES ({0}, {1})", addressId, personId)

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection
        command.ExecuteNonQuery()
        _connection.Close()
    End Sub

    Public Sub InsertProduct(values As Dictionary(Of String, String))
        _connection.Open()

        _query = String.Format("INSERT INTO Product (name, value, color, size, material) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", values.Item("name"), values.Item("value"), values.Item("colour"), values.Item("size"), values.Item("material"))

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection
        Dim rs = command.ExecuteNonQuery()
        _connection.Close()
    End Sub

    Public Sub InsertEmployee(values As Dictionary(Of String, String))
        _connection.Open()
        Dim firstAid As Integer = If(Convert.ToBoolean(values.Item("first_aid")), 1, 0)
        Dim personId = InsertPersonAndGetId(values)
        Dim addressId = InsertAddressAndGetId(values)
        _query = String.Format("INSERT INTO Employee (person_id, address_id, first_aid, start_date, end_date, position) VALUES ({0}, {1}, '{2}', '{3}', '{4}', '{5}')", personId, addressId, firstAid, values.Item("start_date"), values.Item("end_date"), values.Item("position"))

        Dim command As New SqlCeCommand()
        command.CommandText = _query
        command.Connection = _connection
        command.ExecuteNonQuery()
        _connection.Close()
    End Sub

    Public Function GetAllCustomers() As BindingSource
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlCeDataAdapter()
        dataAdapter.SelectCommand = New SqlCeCommand("SELECT c.id as Customer_ID, p.name, p.surname, p.mobile, p.home_tel, a.town, a.postcode, a.line1 as Address_Line_1, a.line2 as Address_Line_2 FROM Customer c LEFT JOIN Person p on c.person_id = p.id LEFT JOIN Address a on c.address_id = a.id")
        dataAdapter.SelectCommand.Connection = _connection
        _connection.Open()
        dataAdapter.Fill(dataSet, "customer")
        _connection.Close()

        Dim bindingSource As New BindingSource
        bindingSource.DataSource = dataSet
        bindingSource.DataMember = "customer"

        Return bindingSource
    End Function

    Public Function GetAllProducts() As BindingSource
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlCeDataAdapter()
        dataAdapter.SelectCommand = New SqlCeCommand("SELECT id, name, value, material, size, color FROM Product")
        dataAdapter.SelectCommand.Connection = _connection
        _connection.Open()
        dataAdapter.Fill(dataSet, "product")
        _connection.Close()

        Dim bindingSource As New BindingSource
        bindingSource.DataSource = dataSet
        bindingSource.DataMember = "product"

        Return bindingSource
    End Function

    Public Function GetAllEmployees() As BindingSource
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlCeDataAdapter()
        dataAdapter.SelectCommand = New SqlCeCommand("SELECT p.name + ' ' + p.surname AS fullName, p.name, p.surname, a.town, a.postcode, a.line1 as Address_Line_1, a.line2 as Address_Line_2, p.home_tel, p.mobile, e.start_date, e.end_date, e.first_aid, e.position, e.id AS Employee_ID FROM Employee e LEFT JOIN Person p on e.person_id = p.id LEFT JOIN Address a on e.address_id = a.id")
        dataAdapter.SelectCommand.Connection = _connection
        _connection.Open()
        dataAdapter.Fill(dataSet, "employee")
        _connection.Close()

        Dim bindingSource As New BindingSource
        bindingSource.DataSource = dataSet
        bindingSource.DataMember = "employee"

        Return bindingSource
    End Function

    Public Function GetAllOrders() As BindingSource
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlCeDataAdapter()
        dataAdapter.SelectCommand = New SqlCeCommand("select o.id as Order_ID, op.quantity, p.name, p.surname, p.mobile, pr.name, pr.color, pr.value from [Order] o left join Order_Product op ON op.order_id = o.id left join Customer c ON c.id = o.customer_id left join Person p ON p.id = c.person_id left join Product pr ON pr.id = op.product_id where op.quantity IS NOT NULL")
        dataAdapter.SelectCommand.Connection = _connection
        _connection.Open()
        dataAdapter.Fill(dataSet, "order")
        _connection.Close()

        Dim bindingSource As New BindingSource
        bindingSource.DataSource = dataSet
        bindingSource.DataMember = "order"

        Return bindingSource
    End Function

    Public Function FindEmployees(name As String, surname As String, town As String, postcode As String, position As String) As BindingSource
        Dim bs = GetAllEmployees()
        bs.Filter = "name LIKE '%" + name + "%'" + " AND " + "surname LIKE '%" + surname + "%'" + " AND " + "town LIKE '%" + town + "%'" + " AND " + "postcode LIKE '%" + postcode + "%'" + " AND " + "position LIKE '%" + position + "%'"
        Return bs
    End Function

    Public Function FindCustomer(name As String, surname As String, town As String, postcode As String) As BindingSource
        Dim bs = GetAllCustomers()
        bs.Filter = "name LIKE '%" + name + "%'" + " AND " + "surname LIKE '%" + surname + "%'" + " AND " + "town LIKE '%" + town + "%'" + " AND " + "postcode LIKE '%" + postcode + "%'"
        Return bs
    End Function

    Public Function FindProduct(name As String, material As String, colour As String, id As String) As BindingSource
        Dim bs = GetAllProducts()
        Dim query As String = "name LIKE '%" + name + "%'" + " AND " + "material LIKE '%" + material + "%'" + " AND " + "color LIKE '%" + colour + "%'"
        If Not (String.IsNullOrEmpty(id)) Then
            query = "ID = " + id + " AND " + query
        End If
        bs.Filter = query
        Return bs
    End Function

    Public Function FindOrder(orderId As String) As BindingSource
        Dim bs = GetAllOrders()
        If Not (String.IsNullOrEmpty(orderId)) Then
            bs.Filter = "Order_ID = " + orderId
        End If
        Return bs
    End Function
    Public Sub Close()
        _connection.Close()
    End Sub

End Class
